package org.lq.shopping.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.lq.shopping.dao.ShopShippingAddressDao;
import org.lq.shopping.entity.ShopShippingAddress;
import org.lq.shopping.util.DruidUtil;

/** 
* @author zyq1397860074@qq.com 
* @version 创建时间：2018年9月26日 下午12:06:57 
*
*/
public class ShopShippingAddressDaoImpl implements ShopShippingAddressDao {
	Logger log = Logger.getLogger(ShopShippingAddressDaoImpl.class);
	QueryRunner qr=new QueryRunner(DruidUtil.getDataSource());
	@Override
	public int add(ShopShippingAddress t) {
		int num=0;
		String sql="insert into shop_shipping_address "+
				"(address_id,user_id,location,detailed_address,receive_name,receive_phone,receive_address)"+
				"values"+
				"(shipping_address_seq.nextval,?,?,?,?,?,?)";
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + t + "]");
		try {
			num=qr.execute(sql,t.getUser_id(),t.getLocation(), t.getDetailed_address(),t.getReceive_name(),t.getReceive_phone(),t.getReceive_address());
		} catch (SQLException e) {
			log.error(e);
		}
		return num;
	}

	@Override
	public int delete(int id) {
		int num=0;
		String sql="select * from shop_shipping_address where address_id =?";
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + id + "]");
		try {
			num=qr.execute(sql,id);
		} catch (SQLException e) {
			log.error(e);
		}
		return num;
	}

	@Override
	public int update(ShopShippingAddress t) {
		int num=0;
		String sql=
				"update shop_shipping_address" +
						"       user_id = ?" + 
						"       location = ?" + 
						"       detailed_address = ?" + 
						"       receive_name = ?" + 
						"       receive_phone = ?" + 
						"       receive_address = ?" + 
						" where address_id =?";
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + t + "]");
		 try {
			num=qr.execute(sql,t.getUser_id(),t.getLocation(),t.getDetailed_address(),t.getReceive_name(),t.getReceive_phone(),t.getReceive_address(),t.getAddress_id());
		} catch (SQLException e) {
			log.error(e);
		}
		return num;
	}

	@Override
	public List<ShopShippingAddress> findAll() {
		List<ShopShippingAddress>list = new ArrayList<>();
		String sql = "select * from shop_shipping_address order by address_id";
		log.debug("SQL语句:[" + sql + "]");
		try {
			list = qr.query(sql, new BeanListHandler<ShopShippingAddress>(ShopShippingAddress.class));
		} catch (SQLException e) {
			log.error(e);
		}
		return list;
	}

	@Override
	public ShopShippingAddress findById(int id) {
		String sql = "select * from shop_shipping_address where address_id=?";
		ShopShippingAddress address=null;
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + id + "]");
		try {
			address = qr.query(sql, new BeanHandler<ShopShippingAddress>(ShopShippingAddress.class),id);
		} catch (SQLException e) {
			log.error(e);
		}
		return address;
	}

	@Override
	public List<ShopShippingAddress> LikeColumnByValue(String columName, Object value) {
		List<ShopShippingAddress>list = new ArrayList<>();
		String sql = "select * from shop_shipping_address where "+columName+"= ?";
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + columName+"---"+value + "]");
		try {
			list = qr.query(sql, new BeanListHandler<ShopShippingAddress>(ShopShippingAddress.class),value);
		} catch (SQLException e) {
			log.error(e);
		}
		return list;
	}

	@Override
	public int getCount() {
		int num=0;
		String sql ="select count(1) from shop_shipping_address";
		log.debug("SQL语句:[" + sql + "]");
		try {
			num = qr.query(sql, new ScalarHandler<BigDecimal>()).intValue();
		} catch (SQLException e) {
			log.error(e);
		}
		return num;
	}

	@Override
	public List<ShopShippingAddress> findPage(int pageIndex, int pageSize) {
		List<ShopShippingAddress> list = null;
		String sql = "select * from "
				+ "(select rownum r,t.* from "
				+ "(select * from shop_shipping_address order by address_id) t"
				+ " where rownum<=?)"
				+ "where r > ?";
		int max = pageIndex*pageSize;
		int min = (pageIndex-1) * pageSize;
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + pageIndex+"-----"+pageSize + "]");
		try {
			list = qr.query(sql, new BeanListHandler<ShopShippingAddress>(ShopShippingAddress.class),max,min);
		} catch (SQLException e) {
			log.error(e);
		}
		return list;
	}

	@Override
	public List<ShopShippingAddress> getShopShippingAddressByUserId(int id) {
		List<ShopShippingAddress>list = new ArrayList<>();
		String sql = "select * from shop_shipping_address where user_id = ?";
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + id + "]");
		try {
			list = qr.query(sql, new BeanListHandler<ShopShippingAddress>(ShopShippingAddress.class),id);
		} catch (SQLException e) {
			log.error(e);
		}
		return list;
	}

}
